import datetime
from sqlalchemy import Column, Integer, String, ForeignKey, Date, Table, MetaData, Text
from sqlalchemy.orm import relationship
from flask_appbuilder import Model

mindate = datetime.date(datetime.MINYEAR, 1, 1)

_mt = MetaData()


class ContactGroup(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return self.name


class Gender(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique = True, nullable=False)

    def __repr__(self):
        return self.name

class ContactGroupView(Model):
    """
        First create the view on SQL:

        create view contact_group_view as select g.id as id, count(*) as group_count, g.name as group_name from contact_group as g, contact as c where c.contact_group_id=g.id group by g.name;
    """
    __table__ = Table(
        'contact_group_view', _mt,
        Column('id', Integer, primary_key=True),
        Column('group_count', Integer),
        Column('group_name', String(150))
    )


class Contact(Model):
    id = Column(Integer, primary_key=True)
    name =  Column(String(150), unique = True, nullable=False)
    address = Column(String(564))
    birthday = Column(Date, nullable=True)
    personal_phone = Column(String(20))
    personal_celphone = Column(String(20))
    contact_group_id = Column(Integer, ForeignKey('contact_group.id'), nullable=False)
    contact_group = relationship("ContactGroup")
    gender_id = Column(Integer, ForeignKey('gender.id'), nullable=False)
    gender = relationship("Gender")

    def __repr__(self):
        return self.name

    def month_year(self):
        date = self.birthday or mindate
        return datetime.datetime(date.year, date.month, 1) or mindate

    def year(self):
        date = self.birthday or mindate
        return datetime.datetime(date.year, 1, 1)
        
